﻿using Microsoft.AspNet.Identity;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace PublSystem
{
    public partial class New : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        protected void ButbuttonSubmit_Click(object sender, EventArgs e)
        {
            int pubId = 0;
            int kwId = 0;
            int authId = 0;
            using (
                SqlConnection sqlconn =
                    new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
            {
                sqlconn.Open();
                using (
                    SqlCommand cmd =
                        new SqlCommand(
                            "INSERT INTO PublicationsInfo.Publications(title, summary, link, published, updated) OUTPUT INSERTED.publicationId VALUES (@title, @text, @link, @published, @updated);",
                            sqlconn))
                {
                    SqlParameter paramTitle = new SqlParameter();
                    paramTitle.ParameterName = "@title";
                    paramTitle.Value = txtTitle.Text;
                    SqlParameter paramText = new SqlParameter();
                    paramText.ParameterName = "@text";
                    paramText.Value = txtContent.Text;
                    SqlParameter paramLink = new SqlParameter();
                    paramLink.ParameterName = "@link";
                    paramLink.Value = "Publication?id=";
                    SqlParameter paramPublished = new SqlParameter();
                    paramPublished.ParameterName = "@published";
                    paramPublished.Value = DateTime.Now;
                    SqlParameter paramUpdated = new SqlParameter();
                    paramUpdated.ParameterName = "@updated";
                    paramUpdated.Value = DateTime.Now;

                    cmd.Parameters.Add(paramTitle);
                    cmd.Parameters.Add(paramText);
                    cmd.Parameters.Add(paramLink);
                    cmd.Parameters.Add(paramPublished);
                    cmd.Parameters.Add(paramUpdated);
                    pubId = (int)cmd.ExecuteScalar();

                    SqlCommand cmdDeleteAuthorsLinks =
                        new SqlCommand(
                            "DELETE FROM PublicationsInfo.AuthorsPublications WHERE publicationId=" + pubId,
                            sqlconn);
                    cmdDeleteAuthorsLinks.ExecuteScalar();

                    SqlCommand cmdDeleteKeywordsLinks =
                        new SqlCommand(
                            "DELETE FROM PublicationsInfo.KeywordsPublications WHERE publicationId=" + pubId,
                            sqlconn);
                    cmdDeleteKeywordsLinks.ExecuteScalar();
                }

                

                string addAuthor = @"IF EXISTS (SELECT id FROM PublicationsInfo.Authors WHERE name=@author)
                          BEGIN 
                            SELECT id FROM PublicationsInfo.Authors WHERE name=@author;
                          END 
                        ELSE
                          BEGIN
                            INSERT INTO PublicationsInfo.Authors(name) OUTPUT INSERTED.id VALUES(@author);
                          END";

                using (SqlCommand cmd = new SqlCommand(addAuthor, sqlconn))
                {
                    SqlParameter kwpar = new SqlParameter();
                    kwpar.ParameterName = "@author";
                    kwpar.Value = (Context.User.Identity.GetUserName() == "" || Context.User.Identity.GetUserName() == null) ? "Anonymous" : Context.User.Identity.GetUserName();
                    cmd.Parameters.Add(kwpar);
                    try
                    {
                        authId = (int)cmd.ExecuteScalar();
                    }
                    catch
                    {
                    }
                }

                using (
                    SqlCommand cmd =
                        new SqlCommand(
                            string.Format(
                                "INSERT INTO PublicationsInfo.AuthorsPublications(authorId, publicationId) VALUES ({0}, {1})",
                                authId, pubId)))
                {
                    cmd.Connection = sqlconn;
                    try
                    {
                        cmd.ExecuteScalar();
                    }
                    catch
                    {
                    }
                    finally
                    {
                        Response.Redirect("Publication?id=" + pubId);
                    }
                }
            }
        }
    }
}